Skip Headers

Oracle® HTML DB 2 Day Developer Release 2.0 Part Number B16376-01 |
Home | Book List | Contents | Master Index | Feedback

Previous | Next
| View PDF

# 7 How to Create a Stacked Bar Chart

A stacked bar chart displays the results of multiple queries stacked on top of one another (either vertically or horizontally). Using a stacked bar chart is an effective way to communicate the absolute values of data points represented by the segments of each bar, as well as the total value represented by data points from each series stacked in a bar.

In Oracle HTML DB, stacked bar chart is only available as an SVG chart. To create a stacked bar chart, you can create the chart as a stacked bar chart, or you can create a regular (non-HTML) bar chart and then add queries to it.

This tutorial describes how to create a stacked bar chart using existing data within the demonstration application, Sample Application.

This section contains the following topics:

- Accessing Sample Application

- Creating a Stacked Bar Chart

- Adding Additional Series

- Changing the Chart Format

- Viewing the Chart

## Accessing Sample Application

To access Sample Application:

1. Log in to Oracle HTML DB.

2. Click the down arrow on the right side of the Application Builder icon.

3. From the menu, select Demonstrations.

   The Demonstration Applications page appears.

4. Locate Sample Application and check the Status column:

   1. If the Status column displays Installed, return to the Workspace home page.

   2. If the Status column displays Not Installed, select Install in the Action column.

   3. Follow the on-screen instructions.

5. When prompted, enter the appropriate username and password and click Login

   - For User Name, enter either `demo` or `admin`.

   - For Password, enter the name of the current workspace using all lowercase letters.

## Creating a Stacked Bar Chart

To create the initial report, you can either add a region to an existing page and define it as a stacked bar chart, or you can create a new page. In this exercise, you create a new page within the Sample Application.

The chart will display the sum by product category for sales from within the Sample Application. It will contain sales for the twelve months prior to the current month. In the following exercise, you will create four queries (called series) for each of the product categories (phones, computers, audio, and video).

To create a new page:

1. Navigate to the Workspace home page.

2. Click the Application Builder icon.

3. Select Sample Application.

4. Click Create Page.

5. Select Chart and click Next.

6. Select Stacked Bar, Vertical and click Next.

7. For Page Attributes:

   1. For Page, enter `750`.

   2. For Page Name, enter `Revenue by Category`.

   3. For Region Name, enter `Revenue by Category`.

   4. Click Next.

8. For Tab Options, accept the default Do not use Tabs and click Next.

9. For Query:

   1. For Series Name, enter `Phones`.

   2. In SQL, enter:

          SELECT NULL l,
                 sales_month,
                 revenue
             FROM (
          SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
                 SUM(oi.quantity * oi.unit_price) revenue,
                 TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
            FROM DEMO_PRODUCT_INFO p,
                 DEMO_ORDER_ITEMS oi,
                 DEMO_ORDERS o
           WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
             AND o.order_timestamp > (trunc(sysdate-365,'MON'))
             AND o.order_id = oi.order_id
             AND oi.product_id = p.product_id
             AND p.category = 'Phones'
          GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
          ORDER BY sales_month_order
          )

      The syntax for the select of a chart is `SELECT link, label, value`. You must have all three and only three items in your select. Because there is no appropriate page to link to, the link is defined as null.

      You cannot have an order by in the select statement for series in a stacked chart. The information will be displayed in alphabetical order and this does not work for dates (October displays before September). To get the data to display in chronological order, you need to order the data inside a nested select.

   3. For When No Data Found Message, enter:

          No orders found in the past 12 months.

   4. Click Next.

10. Review your selections and click Finish.

## Adding Additional Series

Once you have created the new page with a region defining the query, you need to add the series. In the following exercise, you will add a series for each of the categories of product in the application (that is, computers, audio, and video). Note you have already created the phones category.

To add a series for computers category:

1. On the Success Page, select Edit Page.

   The Page Definition for page 750 appears.

2. Under Regions, select Chart to the left of Revenue by Category.

   Under Chart Series, notice that only one series appears.

3. Add a chart series for Computer. Under Chart Series, click Add Series.

   1. For Series Name, enter `Computer`.

   2. In SQL, enter:

          SELECT NULL l,
                 sales_month,
                 revenue
             FROM (
          SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
                 SUM(oi.quantity * oi.unit_price) revenue,
                 TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
            FROM DEMO_PRODUCT_INFO p,
                 DEMO_ORDER_ITEMS oi,
                 DEMO_ORDERS o
           WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
             AND o.order_timestamp > (trunc(sysdate-365,'MON'))
             AND o.order_id = oi.order_id
             AND oi.product_id = p.product_id
             AND p.category = 'Computer'
          GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
          ORDER BY sales_month_order
          )

      Note that this SQL matches the previous series. The only difference is that the category in the `WHERE` clause.

   3. For When No Data Found Message, enter:

          No orders found in the past 12 months.

   4. Click Apply Changes.

4. Add a chart series for Audio. Under Chart Series, click Add Series.

   1. For Series Name, enter `Audio`.

   2. In SQL, enter:

          SELECT NULL l,
                 sales_month,
                 revenue
             FROM (
          SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
                 SUM(oi.quantity * oi.unit_price) revenue,
                 TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
            FROM DEMO_PRODUCT_INFO p,
                 DEMO_ORDER_ITEMS oi,
                 DEMO_ORDERS o
           WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
             AND o.order_timestamp > (trunc(sysdate-365,'MON'))
             AND o.order_id = oi.order_id
             AND oi.product_id = p.product_id
             AND p.category = 'Audio'
          GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
          ORDER BY sales_month_order
          )

   3. For When No Data Found Message, enter:

          No orders found in the past 12 months.

   4. Click Apply Changes.

5. Add a chart series for Video. Under Chart Series, click Add Series.

   1. For Series Name, enter `Video`.

   2. In SQL, enter:

          SELECT NULL l,
                 sales_month,
                 revenue
             FROM (
          SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
                 SUM(oi.quantity * oi.unit_price) revenue,
                 TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
            FROM DEMO_PRODUCT_INFO p,
                 DEMO_ORDER_ITEMS oi,
                 DEMO_ORDERS o
           WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
             AND o.order_timestamp > (trunc(sysdate-365,'MON'))
             AND o.order_id = oi.order_id
             AND oi.product_id = p.product_id
             AND p.category = 'Video'
          GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
          ORDER BY sales_month_order
          )

   3. For When No Data Found Message, enter:

          No orders found in the past 12 months.

   4. Click Apply Changes.

## Changing the Chart Format

Next, you will enhance the appearance of the chart with axis titles by adding a region footer.

To format the y-axis:

1. Scroll down to Axes Settings.

2. In y-axis Format, enter:

       FML999G999G999G999G990

   This formats the `sales_month` column as money, but without displaying the cents

3. Select the Region Definition tab.

4. Scroll down to Header and Footer.

5. In Region Footer, enter:

       Note: This reflects sales for the 12 months prior to the current month.

6. Click Apply Changes.

## Viewing the Chart

Now that the chart is complete, you can view it.

To run the chart:

1. Click the Run Page icon in the upper right corner of the page.

2. If you have already run Sample Application in this session, you will be taken to page 750. Otherwise, enter the appropriate username and password and click Login

   - For User Name, enter either `demo` or `admin`

   - For Password, enter the name of the current workspace using all lowercase letters

3. Navigate to page 750.

   As shown in Figure 7-1, the Revenue by Category chart appears.

   Figure 7-1 Revenue by Category Bar Chart

   Description of the illustration bar_chart.gif

Tip: One way to navigate to a new page within a running application is to change the second parameter (the page identifier) to 750. For example, you would change:
    http://htmldb.oraclecorp.com/pls/htmldb/f?p=2046:1: ...
to
    http://htmldb.oraclecorp.com/pls/htmldb/f?p=2046:750: ...